package hutech.ksv.dao;

import hutech.ksv.entity.NhaTroEntity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class NhaTroDao implements Serializable {

	@Autowired
	DataSource dataSource;

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	// Test connection
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/mydb", "root", "mysql@123");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// Insert Nha Tro
	public void insertNhaTro(NhaTroEntity nhatro) {
		String query = "INSERT INTO `NhaTro` (`DiaChiNT`, `GiaThueMotThang`, `MoTaNT`, `TrangThaiThue`, `TrangThaiDuyetNT`, `TenTKCNT`, `TenTKQTV`, `HinhAnhNT`) VALUES (?,?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setString(1, nhatro.getDiaChiNT());
			preparedStatement.setFloat(2, nhatro.getGiaThueMotThang());
			preparedStatement.setString(3, nhatro.getMoTaNT());
			preparedStatement.setBoolean(4, nhatro.isTrangThaiThue());
			preparedStatement.setBoolean(5, nhatro.isTrangThaiDuyetNT());
			preparedStatement.setString(6, nhatro.getTenTKCNT());
			preparedStatement.setString(7, nhatro.getTenTKQTV());
			preparedStatement.setString(8, nhatro.getHinhAnhNT());

			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// Update Nha Tro
	public void updateNhaTro(NhaTroEntity nhatro) {
		String query = "UPDATE `NhaTro` "
				+ "SET `DiaChiNT`=?,"
				+ "`GiaThueMotThang`=?,`MoTaNT`=?,`TrangThaiThue`=?,`TrangThaiDuyetNT`=?,"
				+ "`TenTKCNT`=?,`TenTKQTV`=?, `HinhAnhNT`=?" + " WHERE `MaNT`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);

			preparedStatement.setInt(9, nhatro.getMaNT());
			preparedStatement.setString(1, nhatro.getDiaChiNT());
			preparedStatement.setFloat(2, nhatro.getGiaThueMotThang());
			preparedStatement.setString(3, nhatro.getMoTaNT());
			preparedStatement.setBoolean(4, nhatro.isTrangThaiThue());
			preparedStatement.setBoolean(5, nhatro.isTrangThaiDuyetNT());
			preparedStatement.setString(6, nhatro.getTenTKCNT());
			preparedStatement.setString(7, nhatro.getTenTKQTV());
			preparedStatement.setString(8, nhatro.getHinhAnhNT());

			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// Load thong tin nha tro
	public List<NhaTroEntity> loadThongTinNT() {
		String query = "SELECT * FROM `NhaTro`";
		List<NhaTroEntity> listThongTinNT = new ArrayList<NhaTroEntity>();

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				listThongTinNT.add(new NhaTroEntity(rs.getInt(1), rs.getString(2), rs.getFloat(3), 
													rs.getString(4), rs.getBoolean(5), rs.getBoolean(6), 
													rs.getString(7), rs.getString(8), rs.getString(9)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listThongTinNT;
	}

	// Load thong tin ma nha tro
	public List<NhaTroEntity> loadMaNT() {
		String query = "SELECT `MaNT` FROM `NhaTro`";
		List<NhaTroEntity> listMaNT = new ArrayList<NhaTroEntity>();

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				listMaNT.add(new NhaTroEntity(rs.getInt(1)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listMaNT;
	}
	
	// Load danh sach Nha Tro tim theo gia thue
	public List<NhaTroEntity> loadDSNTTheoGia(NhaTroEntity nhatro) {
		float giaThue = nhatro.getGiaThueMotThang();
		List<NhaTroEntity> listDS = new ArrayList<NhaTroEntity>();
		String query = "SELECT * FROM `NhaTro` WHERE `GiaThueMotThang`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setFloat(1, giaThue);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				listDS.add(new NhaTroEntity(rs.getInt(1), rs.getString(2), rs.getFloat(3), 
						rs.getString(4), rs.getBoolean(5), rs.getBoolean(6), 
						rs.getString(7), rs.getString(8), rs.getString(9)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listDS;
	}

	// Load danh sach Nha Tro tim theo dia chi
	public List<NhaTroEntity> loadDSNTTheoDC(NhaTroEntity nhatro) {
		String diaChi = nhatro.getDiaChiNT();
		List<NhaTroEntity> listDS = new ArrayList<NhaTroEntity>();
		String query = "SELECT * FROM `NhaTro` WHERE `DiaChiNT` = ?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, diaChi);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				listDS.add(new NhaTroEntity(rs.getInt(1), rs.getString(2), rs.getFloat(3), 
						rs.getString(4), rs.getBoolean(5), rs.getBoolean(6), 
						rs.getString(7), rs.getString(8), rs.getString(9)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listDS;
	}
}
